﻿using System.Collections.ObjectModel;
using System.Data.Common;
using System.Data.OleDb;
using System.Linq;
using System.Reflection;
using System.Runtime.InteropServices;
using JiraGenCommon.JiraServices;
using JiraGenCommon.Model;
using System;
using System.Collections.Generic;
using System.Data;
using Microsoft.Office.Interop.Excel;
using JiraGenCommon.Constants;

namespace JiraGenCommon.Helpers
{
    public static class ExcelHelper
    {
        private const int MaxLength = 255;

        private static ICollection<JiraTask> _syncList = new List<JiraTask>();
        public static ICollection<JiraTask> SyncList
        {
            get { return _syncList; }
            internal set { _syncList = value; }
        }

        public static IEnumerable<PblItem> GetSheetData(string excelFile, bool isNewExtension, string sheetName)
        {
            var sheetData = new List<PblItem>();

            var connectionString = string.Format(isNewExtension ?
                "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0 Xml;IMEX=1;HDR=YES;\"" :
                "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 8.0;IMEX=1;HDR=YES;\"", excelFile);

            using (var connection = new OleDbConnection(connectionString))
            {
                using (DbCommand command = connection.CreateCommand())
                {
                    command.CommandText = string.Format("SELECT * FROM [{0}]", sheetName);
                    connection.Open();
                    using (DbDataReader dr = command.ExecuteReader())
                    {
                        int i = 2;
                        while (dr.Read())
                        {
                            var pblItem = new PblItem();
                            pblItem.Scope = dr[0].ToString();
                            pblItem.Section = dr[1].ToString();
                            pblItem.Header = dr[2].ToString();
                            pblItem.UserStory = UtilsHelper.GetStringWithLength(dr[3], MaxLength);
                            pblItem.Jira = dr[4].ToString();
                            pblItem.Status = dr[5].ToString();
                            pblItem.MoreInfo = dr[6].ToString();
                            pblItem.ConditionsToSatisfy = dr[7].ToString();
                            pblItem.Estimation = dr[8].ToString();
                            pblItem.DevEffort = dr[9].ToString();
                            pblItem.Responsible = dr[10].ToString();
                            pblItem.Impact = dr[11].ToString();
                            pblItem.Move = dr[12].ToString();
                            pblItem.ID = dr[13].ToString();
                            pblItem.RowNumber = i;

                            sheetData.Add(pblItem);

                            i += 1;
                        }
                    }
                }
            }

            return sheetData;
        }

        public static IEnumerable<string> GetExcelSheetNames(string excelFile, bool isNewExtension)
        {
            var sheets = new List<string>();

            var connectionString = string.Format(isNewExtension ?
                "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0 Xml;IMEX=1;HDR=YES;\"" :
                "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 8.0;IMEX=1;HDR=YES;\"", excelFile);

            using (var connection = new OleDbConnection(connectionString))
            {
                connection.Open();
                using (var dt = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null))
                {
                    if (dt != null)
                    {
                        sheets.Add("Choose sheet");
                        foreach (DataRow row in dt.Rows)
                        {
                            var sheetName = row["TABLE_NAME"].ToString();
                            if (sheetName.EndsWith("$") || sheetName.EndsWith("$'"))
                            {
                                sheetName = UtilsHelper.RefineSheetName(sheetName);
                                sheets.Add(sheetName);
                            }
                        }
                    }

                }
            }

            return sheets;
        }

        public static List<JiraGenContract> ExcelToJiraParser(string fileName, string projectName, bool isNewFormat, string sheetName)
        {
            if (projectName == null) throw new ArgumentNullException("projectName");

            var excelSheetRows = GetSheetData(fileName, isNewFormat, sheetName);

            _syncList = new Collection<JiraTask>();

            if (excelSheetRows == null) throw new ArgumentNullException("excelSheetRowsMsg");

            var listJiraGenContract = new List<JiraGenContract>();

            // Parser code here
            JiraGenContract jiraGenContract = null;
            int idx = 1;
            foreach (var pbl in excelSheetRows)
            {
                if (IsSprintRow(pbl))
                {
                    if (jiraGenContract != null)
                    {
                        listJiraGenContract.Add(jiraGenContract);
                    }

                    jiraGenContract = new JiraGenContract { Sprints = UtilsHelper.GetStringField(pbl.UserStory) };
                }
                else
                {
                    if (jiraGenContract == null)
                    {
                        jiraGenContract = new JiraGenContract();
                    }

                    var jiraTask = JiraHelper.CreateJiraTask(projectName, UtilsHelper.GetStringField(pbl.UserStory),
                                              string.Format("{0}{1}{2}", pbl.UserStory,
                                              pbl.MoreInfo, pbl.ConditionsToSatisfy),
                                              pbl.Responsible, pbl.Estimation, string.Empty, pbl.RowNumber, pbl.Jira);

                    if(!string.IsNullOrEmpty(pbl.Jira))
                    {
                        _syncList.Add(jiraTask);
                    }

                    if (jiraGenContract.Tasks == null)
                    {
                        jiraGenContract.Tasks = new List<JiraTask>();
                    }

                    // only add subtasks for the Task is not exsit on Jira
                    if (string.IsNullOrEmpty(pbl.Jira))
                    {
                        // Create SubTask 
                        jiraTask.SubTasks = new List<JiraTask>
                                                {
                                                    JiraHelper.CreateJiraTask(projectName, "Development", "Development",
                                                                              pbl.Responsible,pbl.Estimation, string.Empty, pbl.RowNumber, string.Empty),
                                                    JiraHelper.CreateJiraTask(projectName, "Testing", "Testing",
                                                                              string.Empty, string.Empty, string.Empty, pbl.RowNumber, string.Empty)
                                                };
                        
                    }

                    jiraGenContract.Tasks.Add(jiraTask);

                    // 
                    if (idx == excelSheetRows.Count())
                    {
                        listJiraGenContract.Add(jiraGenContract);
                    }
                }

                idx += 1;
            }

            return listJiraGenContract;
        }

        public static void SyncFromJiraToExcel(string fileName, string sheetName, Dictionary<string, string> listUs)
        {
            var application = new ApplicationClass();
            var workBook = application.Workbooks.Open(fileName);
            try
            {
                var inputWorkSheet = workBook.Worksheets.Cast<_Worksheet>().Where(w => w.Name == sheetName).FirstOrDefault();
                var jiraServerUrl = GlobalVariables.Current.JiraServerUrl; //Properties.Settings.Default.JiraGenCommon_jiraSoapServices_JiraServerUrl;

                if (listUs != null && listUs.Count > 0)
                {
                    foreach (var listU in listUs)
                    {
                        var cellSynch = string.Format("E{0}", listU.Key);
                        inputWorkSheet.Cells.SetValue(cellSynch,
                            string.Format("=HYPERLINK(\"{0}/browse/{1}\", \"{2}\")", jiraServerUrl, listU.Value, listU.Value));
                    }
                }

                var issuesStatus = JiraService.Instance.GetStatuses();

                if (_syncList != null && _syncList.Count > 0)
                {
                    var devEffort = UtilsHelper.GetDevEffort(_syncList);

                    foreach (var jiraTask in _syncList)
                    {
                        // sync Log time
                        if (devEffort.ContainsKey(jiraTask.JiraKey))
                        {
                            var cellSynch = string.Format("J{0}", jiraTask.RowNumber);
                            inputWorkSheet.Cells.SetValue(cellSynch, devEffort[jiraTask.JiraKey]);
                        }

                        // get Jira User Story
                        var subTasks = JiraService.Instance.GetUserStorySubTasks(jiraTask.JiraKey);
                        if (subTasks != null && subTasks.Length > 0)
                        {
                            foreach (var issue in subTasks)
                            {
                                var summary = issue.summary.ToLower();
                                if (summary.IndexOf("development") != -1)
                                {
                                    if (issuesStatus.ContainsKey(issue.status))
                                    {
                                        var subTaskStatus = issuesStatus[issue.status]; // default value

                                        var cellSynch = string.Format("F{0}", jiraTask.RowNumber);
                                        inputWorkSheet.Cells.SetValue(cellSynch, subTaskStatus);
                                    }

                                }
                            }
                        }

                    }
                }
            }
            catch (Exception exception)
            {
                LogHelper.Write(exception.Message, LogLevel.Error);
            }
            finally
            {
                workBook.Save();

                // finally close workbook
                workBook.Close();
                Marshal.ReleaseComObject(workBook);

                application.Quit();
                Marshal.ReleaseComObject(application);
            }
        }

        private static bool IsSprintRow(PblItem pblItem)
        {
            return string.IsNullOrEmpty(pblItem.Section) && string.IsNullOrEmpty(pblItem.Header);
        }
    }

    public static class Extensions
    {
        public static void SetValue(this Range range, String cellAddress, object value)
        {
            var cell = range.get_Range(cellAddress);
            Object[] args1 = new Object[1];
            args1[0] = value;
            cell.GetType().InvokeMember("Value", BindingFlags.SetProperty, null, cell, args1);
        }

    }
}
